package uit.jee.lib.access.mapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import uit.jee.lib.dto.ReaderDTO;

/**
 *
 * @author ngloc_it
 */
public class ReaderMapper extends MapperDB{

    public ReaderMapper() throws Exception{
        super();
    }

    private void InitReaderDTOFromRs(ReaderDTO reader, ResultSet  rs) throws Exception{
        if((rs != null) && (reader != null)){
            reader.setReaderId(rs.getString(ReaderDTO.READER_ID));
            reader.setName(rs.getString(ReaderDTO.READER_NAME));
            reader.setAddress(rs.getString(ReaderDTO.READER_ADDRESS));
            reader.setBirthDay(rs.getString(ReaderDTO.READER_BIRTDAY));
            reader.setCMND(rs.getString(ReaderDTO.READER_CMND));
            reader.setEmail(rs.getString(ReaderDTO.READER_EMAIL));
            reader.setJob(rs.getString(ReaderDTO.READER_JOB));
            reader.setOffice(rs.getString(ReaderDTO.READER_OFFICE));
        }
    }

    public boolean CheckExistedReader(String readerId) throws Exception{
        boolean isExisted = false;

        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Select * from booklibrary.docgia where ");
            sql.append(ReaderDTO.READER_ID).append(" = '");
            sql.append(readerId).append("'");
            
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            if((rs != null) && (rs.next()))
                isExisted = true;
        }catch(Exception ex) {
            throw ex;
        }

        return isExisted;
    }

    public boolean CheckExistedReader(ReaderDTO reader) throws Exception{
        boolean isExisted = false;

        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Select * from booklibrary.docgia where ");
            sql.append(ReaderDTO.READER_ID).append(" = '");
            sql.append(reader.getReaderId()).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            if((rs != null) && (rs.next()))
                isExisted = true;
        }catch(Exception ex) {
            throw ex;
        }

        return isExisted;
    }

    public ArrayList<ReaderDTO> GetAllReader() throws Exception{
        ArrayList<ReaderDTO> readers = new ArrayList<ReaderDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Select * from booklibrary.docgia");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            while((rs != null) && (rs.next())){
                ReaderDTO reader = new ReaderDTO();
                InitReaderDTOFromRs(reader, rs);
                readers.add(reader);
            }
        }catch(Exception ex) {
            throw ex;
        }
        return readers;
    }

    public ArrayList<ReaderDTO> GetReaderByName(String readerName) throws Exception{
        ArrayList<ReaderDTO> readers = new ArrayList<ReaderDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Select * from booklibrary.docgia where ");
            sql.append(ReaderDTO.READER_NAME).append(" = '");
            sql.append(readerName).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            while((rs != null) && (rs.next())){
                ReaderDTO reader = new ReaderDTO();
                InitReaderDTOFromRs(reader, rs);
                readers.add(reader);
            }
        }catch(Exception ex) {
            throw ex;
        }
        return readers;
    }

    public ReaderDTO GetReaderById(String readerId) throws Exception{
        ReaderDTO reader = new ReaderDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Select * from booklibrary.docgia where ");
            sql.append(ReaderDTO.READER_ID).append(" = '");
            sql.append(readerId).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            if((rs != null) && (rs.next())){
                InitReaderDTOFromRs(reader, rs);
            }
        }catch(Exception ex) {
            throw ex;
        }
        return reader;
    }

    public ReaderDTO GetReaderByCMND(String cmnd) throws Exception{
        ReaderDTO reader = new ReaderDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Select * from booklibrary.docgia where ");
            sql.append(ReaderDTO.READER_CMND).append(" = '");
            sql.append(cmnd).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            if((rs != null) && (rs.next())){
                InitReaderDTOFromRs(reader, rs);
            }
        }catch(Exception ex) {
            throw ex;
        }
        return reader;
    }

    public void Update(ReaderDTO reader) throws Exception{
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Update booklibrary.docgia Set ");
            sql.append(" " + ReaderDTO.READER_NAME + " = '").append(reader.getName()).append("', ");
            sql.append(" " + ReaderDTO.READER_ADDRESS + " = '").append(reader.getAddress()).append("',");
            sql.append(" " + ReaderDTO.READER_BIRTDAY + " = '").append(reader.getBirthDay()).append("',");
            sql.append(" " + ReaderDTO.READER_CMND + " = '").append(reader.getCMND()).append("', ");
            sql.append(" " + ReaderDTO.READER_EMAIL + " = '").append(reader.getEmail()).append("',");
            sql.append(" " + ReaderDTO.READER_JOB + " = '").append(reader.getJob()).append("',");
            sql.append(" " + ReaderDTO.READER_OFFICE + " = '").append(reader.getOffice()).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
    }

    public void Udpate(ReaderDTO[] readers) throws Exception{

    }

    public void Insert(ReaderDTO reader) throws Exception{
        /**
            INSERT INTO table_name (column1, column2, column3,...)
            VALUES (value1, value2, value3,...)
         */
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("Insert into booklibrary.docgia (");
            sql.append(ReaderDTO.READER_ADDRESS).append(", ");
            sql.append(ReaderDTO.READER_BIRTDAY).append(", ");
            sql.append(ReaderDTO.READER_CMND).append(", ");
            sql.append(ReaderDTO.READER_EMAIL).append(", ");
            sql.append(ReaderDTO.READER_ID).append(", ");
            sql.append(ReaderDTO.READER_JOB).append(", ");
            sql.append(ReaderDTO.READER_NAME).append(", ");
            sql.append(ReaderDTO.READER_OFFICE).append(") values (");

            sql.append("'").append(reader.getAddress()).append("', ");
            sql.append("'").append(reader.getBirthDay()).append("', ");
            sql.append("'").append(reader.getCMND()).append("', ");
            sql.append("'").append(reader.getEmail()).append("', ");
            sql.append("'").append(reader.getReaderId()).append("', ");
            sql.append("'").append(reader.getJob()).append("', ");
            sql.append("'").append(reader.getName()).append("', ");
            sql.append("'").append(reader.getOffice()).append("')");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }
        catch (Exception e) {
            throw e;
        }
    }

    public void Insert(ReaderDTO[] readers) throws Exception{

    }

    public void Delete(ReaderDTO reader) throws Exception{
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Delete * from booklibrary.docgia Where" + ReaderDTO.READER_ID + "= '");
            sql.append(reader.getReaderId()).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
    }

    public void Delete(ReaderDTO[] readers) throws Exception{

    }

    public void Delete(String id) throws Exception{
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Delete * from booklibrary.docgia Where" + ReaderDTO.READER_ID + "= '");
            sql.append(id).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
    }
    
    public void Delete(String[] ids) throws Exception{

    }
}
